def get_team_info(db, logger, team_id: int):
    """获取Team信息
    Args:
        db (_type_): _description_
        logger (_type_): _description_
        team_id (int, optional): _description_. Defaults to Query(..., gt=0).

    Returns:
        _type_: _description_
    """

    try:
        team_info = db.get_one("Team", {"id": team_id})
        if team_info:
            # 获取Team中的用户
            team_members = db.execute_query(
                """
                SELECT ut.*,
                u.name as user_name,
                u.mac_addr as user_mac_addr,
                u.socket_id as user_socket_id,
                u.status as user_status
                FROM User_Team ut
                JOIN User u ON ut.user_id = u.id
                WHERE ut.team_id = ?
                """,
                (team_id,),
            )
            return {**team_info, "members": team_members}
        else:
            raise ValueError(f"Invalid team_id: {team_id}")
    except Exception as e:
        raise e


def create_team(db, logger, request):
    """使用用户的mac地址创建Team
    Args:
        db (_type_): _description_
        logger (_type_): _description_
        request (CreateTeamRequest): Pydantic模型对象

    Returns:
        _type_: _description_
    """
    try:
        # 检查用户ID 是否存在
        validate_users = db.get_all("User", {"mac_addr": request.users})
        if len(validate_users) > 0:
            team_id = db.insert_one("Team", {"name": request.name})
            # 添加成员
            db.insert_many(
                "User_Team", [{"team_id": team_id, "user_id": user_info["id"]} for user_info in validate_users]
            )
            return {"count": len(validate_users)}
        else:
            logger.info("用户ID不存在")
            return {"count": len(validate_users)}
    except Exception as e:
        raise e


def update_team(db, logger, team_id, request):
    """使用用户的mac地址更新Team
    Args:
        db (_type_): _description_
        logger (_type_): _description_
        request (UpdateTeamRequest): Pydantic模型对象

    Returns:
        _type_: _description_
    """
    try:
        team = db.get_one("Team", {"id": team_id})
        if not team:
            raise ValueError(f"Team {team_id} is not exist.")

        if request.team_name:
            db.update_one("Team", {"name": request.team_name}, {"id": team_id})

        # 处理成员更新
        if request.users is not None:
            # 获取更新后的成员列表
            update_users = db.get_all("User", {"mac_addr": request.users})

            # 获取当前成员
            current_users = db.get_all("User_Team", {"team_id": team_id})
            current_member_ids = {member["user_id"] for member in current_users}

            # 计算需要添加和删除的成员
            new_member_ids = set([v["id"] for v in update_users])

            # 需要添加的成员
            to_add = new_member_ids - current_member_ids
            # 需要删除的成员
            to_remove = current_member_ids - new_member_ids

            if to_add:
                db.insert_many("User_Team", [{"team_id": team_id, "user_id": user_id} for user_id in to_add])

            # 删除不需要的成员关联
            if to_remove:
                for user_id in to_remove:
                    db.delete_one("User_Team", {"team_id": team_id, "user_id": user_id})

        logger.info(f"update team: {team_id} success")
        return {"team_id": team_id}
    except Exception as e:
        raise e
